package com.marchsoft.dao;

import com.marchsoft.common.mapper.CommentMapper;
import com.marchsoft.domain.Student;
import com.marchsoft.vo.StuExtra;
import com.marchsoft.vo.StudentExtra;
import org.apache.ibatis.annotations.One;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.mapping.FetchType;
import org.springframework.stereotype.Component;



/**
 * 学生表的mapper接口
 * @author rsw
 */
@Component
public interface StudentMapper extends CommentMapper<Student> {
    /**
     * 查询学生的基本信息
     * 三表联合查询，学生表、教师表、小组表
     * @param accountId
     * @return
     */
    @Select("SELECT s.*,t.name teacher_name,t.email,t.introduction,t.major,g.name group_name,g.major_field,g.intro FROM student s,`group` g,teacher t WHERE s.tid=t.tid AND s.group_id=g.id AND s.zid=#{accountId}")
    @Results({
            @Result(column="name",property="name"),
            @Result(column="group_id",property="groupId"),
            @Result(column="teacher_name",property="teacherName"),
            @Result(column="class_name",property="className"),
            @Result(column="group_name",property="groupName"),
            @Result(column="major_field",property="majorField"),
    })
    StudentExtra queryStudentInfo(Integer accountId);


    /**
     * 查询学生信息（附带专业导师信息、附带所属兴趣小组的信息、附带兴趣小组的老师信息）
     * @param sid 学生id
     * @return 学生信息（附带专业导师信息）
     */
    @Select("SELECT s.*,t.name teacher_name,t.email,t.introduction,t.major,a.account FROM account_pass a,student s LEFT JOIN teacher t  ON s.tid=t.tid WHERE a.zid=s.zid AND s.sid=#{sid}")
    @Results({
            @Result(column="group_id",property="groupId"),
            @Result(column="teacher_name",property="teacherName"),
            @Result(column="class_name",property="className"),
            @Result(column="group_id",property="groupExtra",
                    one=@One(select="com.marchsoft.dao.GroupMapper.queryGroupByGid",
                            fetchType= FetchType.EAGER)),
    })
    StudentExtra queryStudentInfoWithTeacher(Integer sid);

    /**
     * 简单查询学生信息（只附带导师姓名，小组名字等）
     *
     * @param sid
     * @return
     */
    @Select("SELECT s.sid sid,p.account account,s.name stuName,g.name groupName,s.class_name className,t.name teaName FROM account_pass p, student s,teacher t,`group` g WHERE s.sid=#{sid} AND s.group_id =g.id AND s.tid = t.tid AND s.zid = p.zid ")
    @Results({
            @Result(column = "sid", property = "sid"),
            @Result(column = "account", property = "account"),
            @Result(column = "stuName", property = "stuName"),
            @Result(column = "groupName", property = "groupName"),
            @Result(column = "className", property = "className"),
            @Result(column = "teaName", property = "teaName")
    })
    StuExtra queryStudentByStuId(Integer sid);
}
